
package com.zsrt.day0523.jdbc.crud;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

public class OrcaleCRUD {

	/**
	 * 创建表  id username password
	 * @throws Exception
	 */
	@Test
	public void createTable() throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String user = "jiamengju";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		Statement cs = connection.createStatement();
		String sql = "create table user_t(id number,username varchar2(64),password number)";
		boolean b = cs.execute(sql );
		//成功返回的是false
		System.out.println(b);
	}
	
	/**
	 * 插入数据
	 * @throws Exception
	 */
	@Test
	public void insertTable() throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String user = "jiamengju";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		Statement cs = connection.createStatement();
		String sql = "insert into user_t values(1001,'xq',123)";
		int i = cs.executeUpdate(sql);
		System.out.println(i);
	}
	/**
	 * 查询
	 * @param args
	 * @throws Exception
	 */
	@Test
	public void selectTable() throws Exception {
		
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String user = "jiamengju";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		Statement cs = connection.createStatement();
		
		String sql ="select *  from user_t";
		ResultSet query = cs.executeQuery(sql );
		while(query.next()){
			 System.out.println(query.getString("id")+"\t\t"+query.getString("username")+"\t\t"+query.getString("password")); 
		}
	}
	
	
	
	/**
	 * 更改
	 * @throws Exception
	 */
	@Test
	public void updateTest() throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String user = "jiamengju";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		Statement cs = connection.createStatement();
		
		String sql ="update user_t set username='wzl' where username='xqq' ";
		int query = cs.executeUpdate(sql);
		System.out.println(query);
	}
	
	/**
	 * 根据条件删除
	 * @throws Exception
	 */
	@Test
	public void deleteTest() throws Exception {
		
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String user = "jiamengju";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		Statement cs = connection.createStatement();
		
		String sql ="delete user_t s where username='xq' ";
		int query = cs.executeUpdate(sql);
		System.out.println(query);
	}
}
